﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaint_IndexDefrag]
#-- Purpose:		Provides a selective mechanism for index defragging instead
#--					of the brute force attack of the maintenance plan's
#--					integrity checks and optimizations.
#--	Last Update:	10/03/2016
#--					For a complete history - please review comments in Version
#--					control.
#-- Called By:		[dbo].[spMaint_Indexes]
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaint_IndexDefrag]
(
	@database_name	sysname,
	@min_pages		int = 1000
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE	@baseQuery nvarchar(MAX), @sFragChk2k5 nvarchar(MAX), @sSQL nvarchar(4000), 
		@sAuditSQL nvarchar(MAX), @is_enterprise bit, @index_count int
DECLARE @tDefrag TABLE (IndexName sysname, OwnerName sysname, TableName sysname, defragStyle varchar(200), fragPct float)

--- Determine the state of the database
SELECT	@is_enterprise = [dbo].[fnConfig_IsEnterpriseSQL](),
		@index_count = 0

--- Define the query for checking fragmentation
---    NOTE: 5-30 for reorg and >30 for rebuild is straight out of BOL
SELECT	@baseQuery = 'USE [' + @database_name + '];
DECLARE		@db_id int
SET			@db_id = DB_ID()

SELECT		DISTINCT
			i.name IndexName,
			SCHEMA_NAME(t.schema_id) OwnerName,
			t.name TableName,
			(CASE WHEN a.avg_fragmentation_in_percent <= 30 
					AND i.allow_page_locks = 1
					AND (
						EXISTS (SELECT * FROM ' + DB_NAME() + '.[dbo].[vwMaint_LogIndex] WHERE (mnt_index_name = i.name COLLATE SQL_Latin1_General_CP1_CI_AS) AND mnt_action_date >= DATEADD(dd, -7, GETDATE()) AND rebuilds_in_last_week > 0)
						OR (SELECT COUNT(*) FROM ' + DB_NAME() + '.[dbo].[vwMaint_LogIndex] WHERE (mnt_index_name = i.name COLLATE SQL_Latin1_General_CP1_CI_AS) AND mnt_action_date >= DATEADD(dd, -7, GETDATE())) < 6
					)
					THEN ''REORGANIZE;''
					WHEN ' + CAST(@is_enterprise as CHAR(1)) + ' = 1 
						AND (
								SELECT		CAST(COUNT(*) as bit)
								FROM		[sys].[columns] c (NOLOCK)
								LEFT JOIN	[sys].[index_columns] ic (NOLOCK) ON c.object_id = ic.object_id
											AND c.column_id = ic.column_id
											AND ic.index_id = a.index_id
								LEFT JOIN	[sys].[indexes] ii (NOLOCK) ON ii.object_id = c.object_id
											AND ii.index_id = a.index_id
								WHERE		c.object_id = a.object_id
											AND (
												ii.type IN (3, 4) -- 3=XML, 4=Spatial
												OR (
													(
														ii.type = 1		--- Clustered indexes must look at all columns in the table
														OR (			--- Indexes that are not Clustered need only look at columns contained in the index
															ii.type NOT IN (1, 3, 4)
															AND ic.object_id IS NOT NULL
														)
													)
													AND (
														c.system_type_id IN (
															SELECT system_type_id FROM [sys].[types] WHERE name IN (''text'', ''ntext'', ''image'')
														)
														OR (
															c.max_length = -1 
															AND c.system_type_id IN (
																SELECT system_type_id FROM [sys].[types] WHERE name IN (''varbinary'')
															)
														)
														OR (
															CAST(PARSENAME(CONVERT(varchar,SERVERPROPERTY(''ProductVersion'')),4) AS int) <= 10
															AND c.max_length = -1 
															AND c.system_type_id IN (
																SELECT system_type_id FROM [sys].[types] WHERE name IN (''varchar'', ''nvarchar'', ''xml'')
															)
														)
													)
												)
											)
							) = 0  ---- Do not allow online rebuild on indexes that contain blob data
							THEN ''REBUILD WITH (ONLINE=ON);''
					ELSE ''REBUILD;''
					END) defragStyle,
			a.avg_fragmentation_in_percent
FROM		[sys].[dm_db_index_physical_stats] (@db_id, NULL, NULL, NULL, NULL) AS a
JOIN		[sys].[tables] AS t ON a.object_id = t.object_id
JOIN		[sys].[indexes] AS i ON a.object_id = i.object_id
			AND a.index_id = i.index_id
			AND i.index_id > 0
LEFT JOIN	[sys].[dm_db_index_usage_stats] ius ON ius.object_id = i.object_id
			AND i.index_id = ius.index_id
WHERE		a.avg_fragmentation_in_percent >= 5
			AND a.page_count > ' + CAST(@min_pages as varchar(100)) + '
			AND LTRIM(RTRIM(ISNULL(i.name, ''''))) <> ''''
			AND (
				ius.object_id IS NULL
				OR ius.last_user_lookup > DATEADD(dd, -7, GETDATE())
				OR ius.last_user_scan > DATEADD(dd, -7, GETDATE())
				OR ius.last_user_seek > DATEADD(dd, -7, GETDATE())
			)'

--- Retrieve the list of Indexes that didn't fully Defrag
SELECT @sFragChk2k5 = @baseQuery + ' AND i.type = 1'
INSERT INTO @tDefrag EXEC sp_executesql @sFragChk2k5
IF EXISTS (SELECT TOP 1 * FROM @tDefrag)
  BEGIN
	SET	@index_count = @index_count + (SELECT COUNT(*) FROM @tDefrag)

	--- Reorg/Rebuild based on the status of the index
	DECLARE crsIndex CURSOR FOR
		SELECT		'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(OwnerName) + '.' + QUOTENAME(TableName) + ' ' + defragStyle
						+ (CASE WHEN defragStyle = 'REORGANIZE;'
								THEN 'UPDATE STATISTICS ' + QUOTENAME(OwnerName) + '.' + QUOTENAME(TableName) + ' ' + QUOTENAME(IndexName) + ' WITH FULLSCAN;'
								ELSE '' END)
						+ ' --- ' + CAST(fragPct as varchar(20)) + '% fragmentation' [sql],
					'INSERT INTO [dbo].[tblMaint_Log] (mnt_table_catalog, mnt_table_schema, mnt_table_name, mnt_action_date, mnt_action_type, mnt_success, mnt_results) VALUES (''' 
						+ @database_name + ''', ''' + OwnerName + ''', ''' + TableName + ''', ''@@DATE@@'', ''index'',' + '@@STATUS@@, ''@@RESULTS@@'')' [auditSQL]
		FROM		@tDefrag
		ORDER BY	defragStyle DESC,
					OwnerName,
					TableName,
					IndexName
	OPEN crsIndex
	FETCH NEXT FROM crsIndex INTO @sSQL, @sAuditSQL
	WHILE @@FETCH_STATUS <> -1
	  BEGIN
		BEGIN TRY
			--- Ensure the statement is executed in the desired database
			SELECT @sSQL = 'USE [' + @database_name + ']; ' + @sSQL

			--- Execute the Reorg/Rebuild
			PRINT '   ' + @sSQL
			EXEC sp_executesql @sSQL

			--- Add Success Audit
			SELECT @sAuditSQL = REPLACE(REPLACE(REPLACE(@sAuditSQL, '@@STATUS@@', 1), '@@DATE@@', CONVERT(varchar, GETDATE())), '@@RESULTS@@', 'Command: ' + @sSQL)
			EXEC (@sAuditSQL)
		END TRY
		BEGIN CATCH
			PRINT 'The command failed to execute (' + @sSQL + ')'
			PRINT '   Error: ' + CAST(ERROR_NUMBER() as varchar(50)) + ' - ' + ERROR_MESSAGE()
			PRINT '   Procedure: ' + ERROR_PROCEDURE() + ' (line: ' + CAST(ERROR_LINE() as varchar(10)) + ')'

			--- Add Failure Audit
			SELECT @sAuditSQL = REPLACE(REPLACE(@sAuditSQL, '@@STATUS@@', 0), '@@DATE@@', CONVERT(varchar, GETDATE()))
			IF ERROR_NUMBER() IS NOT NULL
				SELECT @sAuditSQL = REPLACE(@sAuditSQL, '@@RESULTS@@', 'Command: ' + @sSQL + CHAR(13)
							+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
							+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
							+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
							+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), ''))
			ELSE
				SELECT @sAuditSQL = REPLACE(@sAuditSQL, '@@RESULTS@@', 'Command: ' + @sSQL)
			EXEC (@sAuditSQL)
		END CATCH

		FETCH NEXT FROM crsIndex INTO @sSQL, @sAuditSQL
	  END
	CLOSE crsIndex
	DEALLOCATE crsIndex
  END
DELETE FROM @tDefrag

--- Process remaining non-clustered indexes
SELECT @sFragChk2k5 = @baseQuery + ' AND i.type <> 1'
INSERT INTO @tDefrag EXEC sp_executesql @sFragChk2k5
IF EXISTS (SELECT TOP 1 * FROM @tDefrag)
  BEGIN
	SET	@index_count = @index_count + (SELECT COUNT(*) FROM @tDefrag)

	--- Reorg/Rebuild based on the status of the index
	DECLARE crsIndex CURSOR FOR
		SELECT		'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(OwnerName) + '.' + QUOTENAME(TableName) + ' ' + defragStyle
						+ (CASE WHEN defragStyle = 'REORGANIZE;'
								THEN 'UPDATE STATISTICS ' + QUOTENAME(OwnerName) + '.' + QUOTENAME(TableName) + ' ' + QUOTENAME(IndexName) + ' WITH FULLSCAN;'
								ELSE '' END)
						+ ' --- ' + CAST(fragPct as varchar(20)) + '% fragmentation' [sql],
					'INSERT INTO [dbo].[tblMaint_Log] (mnt_table_catalog, mnt_table_schema, mnt_table_name, mnt_action_date, mnt_action_type, mnt_success, mnt_results) VALUES (''' 
						+ @database_name + ''', ''' + OwnerName + ''', ''' + TableName + ''', ''@@DATE@@'', ''index'',' + '@@STATUS@@, ''@@RESULTS@@'')' [auditSQL]
		FROM		@tDefrag
	OPEN crsIndex
	FETCH NEXT FROM crsIndex INTO @sSQL, @sAuditSQL
	WHILE @@FETCH_STATUS <> -1
	  BEGIN
		BEGIN TRY
			--- Ensure the statement is executed in the desired database
			SELECT @sSQL = 'USE [' + @database_name + ']; ' + @sSQL

			--- Execute the Reorg/Rebuild
			PRINT '   ' + @sSQL
			EXEC sp_executesql @sSQL

			--- Add Success Audit
			SELECT @sAuditSQL = REPLACE(REPLACE(REPLACE(@sAuditSQL, '@@STATUS@@', 1), '@@DATE@@', CONVERT(varchar, GETDATE())), '@@RESULTS@@', 'Command: ' + @sSQL)
			EXEC (@sAuditSQL)
		END TRY
		BEGIN CATCH
			PRINT 'The command failed to execute (' + @sSQL + ')'
			PRINT '   Error: ' + CAST(ERROR_NUMBER() as varchar(50)) + ' - ' + ERROR_MESSAGE()
			PRINT '   Procedure: ' + ERROR_PROCEDURE() + ' (line: ' + CAST(ERROR_LINE() as varchar(10)) + ')'

			--- Add Failure Audit
			SELECT @sAuditSQL = REPLACE(REPLACE(@sAuditSQL, '@@STATUS@@', 0), '@@DATE@@', CONVERT(varchar, GETDATE()))
			IF ERROR_NUMBER() IS NOT NULL
				SELECT @sAuditSQL = REPLACE(@sAuditSQL, '@@RESULTS@@', 'Command: ' + @sSQL + CHAR(13)
							+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
							+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
							+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
							+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), ''))
			ELSE
				SELECT @sAuditSQL = REPLACE(@sAuditSQL, '@@RESULTS@@', 'Command: ' + @sSQL)
			EXEC (@sAuditSQL)
		END CATCH

		FETCH NEXT FROM crsIndex INTO @sSQL, @sAuditSQL
	  END
	CLOSE crsIndex
	DEALLOCATE crsIndex
  END

IF @index_count = 0
	PRINT 'No indexes found to Reorg/Rebuild in database ' + @database_name

SET NOCOUNT OFF